from flask import Blueprint,request
from model.model import User
from db_init import db
from sqlalchemy import func

user = Blueprint('/user',__name__,url_prefix='/user')

@user.route('/user',methods=["POST"])
def class_creat():
    user = User(name=input('前端随机输入:'),age=input('前端随机输入年龄:'),dormitory=input('前端随机输入宿舍号:'))
    db.session.add(user)
    db.session.commit()
    return '添加成功'

@user.route('/user',methods=["GET"])
def class_get():
      name = request.args.get('name')
      user_da = User.query.filter_by(name=name).first()
      user_data = [{
         'name':user_da.name,
         'age':user_da.age,
         'dormitory':user_da.dormitory
      }]
      ret1 = db.session.query(func.count(User.name)).filter_by(name=User.name).all()
      ret2 = db.session.query(func.avg(User.age)).all()
      print(f'总人数是{ret1}')
      print(f'平均年龄是{ret2}')
      return user_data

@user.route('/user1',methods=["GET"])
def class_get2():
    user = User.query.filter(User.age.__lt__(20))
    list = [{'id': i.id, 'name': i.name, 'age': i.age, 'dormitory': i.dormitory} for i in user]
    age1 = db.session.query(func.avg(User.age)).filter(User.age.__lt__(20)).group_by(User.age.__lt__(20)).all()
    count1 = db.session.query(func.count(User.age)).filter(User.age.__lt__(20)).group_by(User.age.__lt__(20)).all()
    print(f'平均年龄是{age1}')
    print(f'符合条件的总人数是{count1}')
    return {'msg': '获取成功', 'code': 200, 'data': list, 'count': count1[0][0], 'age1': age1[0][0]}

@user.route('/age',methods=["POST","GET"])
def get_age():
    user1 = User.query.filter(User.id.__lt__(20))
    user2 = User.query.filter(User.id.__gt__(20))
    ret1 = db.session.query(func.count(User.name)).filter_by(name=User.name).all()
    ret2 = db.session.query(func.avg(User.age)).all()
    print(user1)
    print(user2)
    print(f'总人数是{ret1}')
    print(f'总人数是{ret2}')
    return '查询成功'



